package com.zsgeek.db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * 
 * 数据库辅助类<BR/>
 * DBUtils<BR/>
 * 创建人:auger <BR/>
 * 时间：2015-3-13-上午11:13:06 <BR/>
 * 
 * @version 1.0.0
 * 
 */
public class DBUtils {

	/**
	 * 
	 * 通过C3P0数据库连接池获取数据库连接对象<BR/>
	 * 需要导入c3p0-0.9.5.jar 和 mchange-commons-java-0.2.9.jar 两个 jar 包<BR/>
	 * 方法名：getConnection<BR/>
	 * 创建人：auger <BR/>
	 * 时间：2015-3-13-上午11:15:50 <BR/>
	 * @return Connection<BR/>
	 * @exception <BR/>
	 * @since 1.0.0
	 */
	public static Connection getConnection() {
		Connection conn = null;
		
		try {
			// 从数据连接池中获取数据库连接对象
			conn = ConnectionManager.getInstance().getConnection();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return conn;
	}

	/**
	 * 
	 * 关闭链接，释放资源<BR/>
	 * 方法名：close<BR/>
	 * 创建人：auger <BR/>
	 * 时间：2015-3-13-上午11:41:56 <BR/>
	 * 
	 * @param connection
	 * @param psmtStatement
	 * @param resultSet
	 *            void<BR/>
	 * @exception <BR/>
	 * @since 1.0.0
	 */
	public static void close(Connection connection, PreparedStatement psmtStatement, ResultSet resultSet) {
		try {
			if (resultSet != null) {
				resultSet.close();
			}
			if (psmtStatement != null) {
				psmtStatement.close();
			}
			if (connection != null) {
				connection.close();
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/**
	 * 
	 * 增删改的通用方法<BR/>
	 * 方法名：executeUpdate<BR/>
	 * 创建人：auger <BR/>
	 * 时间：2015-3-13-上午11:25:48 <BR/>
	 * 
	 * @param sql
	 *            SQL 语句
	 * @param objects
	 *            SQL 参数
	 * @return int 受影响的行数<BR/>
	 * @exception <BR/>
	 * @since 1.0.0
	 */
	public static int executeUpdate(String sql, Object... objects) {
		int result = 0;
		Connection conn = null;
		PreparedStatement psmt = null;
		try {
			conn = getConnection();
			psmt = conn.prepareStatement(sql);
			if (objects != null) {
				for (int i = 0; i < objects.length; i++) {
					psmt.setObject(i + 1, objects[i]);
					System.out.println("参数" + i + "--->" + objects[i]);
				}
			}
			System.out.println("执行增删改的SQL语句是：" + sql);
			result = psmt.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close(conn, psmt, null);
			System.out.println("执行关闭增删改的链接对象");
		}
		return result;
	}

	/**
	 * 
	 * 查询单个字段的通用方法<BR/>
	 * 方法名：executeQuery<BR/>
	 * 创建人：auger <BR/>
	 * 时间：2015-2-13-下午3:02:14 <BR/>
	 * 
	 * @param sql
	 * @param objects
	 * @return Object<BR/>
	 * @exception <BR/>
	 * @since 1.0.0
	 */
	public static Object executeQuery(String sql, Object... objects) {
		return executeQuery(sql, new ResultSetHandler() {
			@Override
			public Object doHandler(ResultSet rs) throws SQLException {
				Object object = null;
				if (rs.next()) {
					object = rs.getObject(1);
				}
				return object;
			}
		}, objects);
	}

	/**
	 * 
	 * 查询的通用方法<BR/>
	 * 方法名：executeQuery<BR/>
	 * 创建人：auger <BR/>
	 * 时间：2015-3-13-上午11:48:03 <BR/>
	 * 
	 * @param sql
	 * @param rsHandler
	 * @param objects
	 * @return Object<BR/>
	 * @exception <BR/>
	 * @since 1.0.0
	 */
	public static Object executeQuery(String sql, ResultSetHandler rsHandler, Object... objects) {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		try {
			connection = getConnection();
			preparedStatement = connection.prepareStatement(sql);
			if (objects != null) {
				for (int i = 0; i < objects.length; i++) {
					preparedStatement.setObject(i + 1, objects[i]);
					System.out.println("参数" + i + "--->" + objects[i]);
				}
			}
			resultSet = preparedStatement.executeQuery();
			System.out.println("执行查询的SQL语句是：" + sql);
			return rsHandler.doHandler(resultSet);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close(connection, preparedStatement, resultSet);
			System.out.println("执行关闭查询的链接对象");
		}
		return null;
	}

}
